package com.zlq.login.dao;

import com.zlq.login.entity.User;
import com.zlq.login.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

public class UserDaoLo {
    public int selectcount() {
        String sql = "SELECT count(id) FROM myuser where status=0";
        try {
            Connection con = DBUtil.getConnection();
            PreparedStatement ps = con.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            int count = 0;
            while (rs.next()) {
                count = rs.getInt(1);
            }
            ps.close();
            rs.close();
            return count;
        } catch (Exception e) {
        }
        return 0;
    }

    public List<User> selectByPage(int numpage,int size){
        String sql = "select * from myuser where status=0 limit ?,?";
        List<User> use = new ArrayList<>();
        try {
            Connection con = DBUtil.getConnection();
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1,numpage);
            ps.setInt(2,size);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String password = rs.getString("password");
                int status = rs.getInt("status");
                User user = new User(id,name, password,status);
                use.add(user);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return use;
    }

    public User selectByName(String name) throws Exception {
        String sql = "select name,password from myuser where name=?";
        List<Map<String, String>> list = DBUtil.query(sql, name);
        User user = null;
        for (Map<String, String> map: list) {
            user = new User(map.get("name"),map.get("password"));
        }
        return  user;
    }

    public List<User> selectByLike(String str){
        String sql = "select id,name,password from myuser where name like ? ";
        List<User> use = new ArrayList<>();
        try {
            Connection con = DBUtil.getConnection();
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1,str);
            ResultSet rs = ps.executeQuery();

            while (rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String password = rs.getString("password");
                User user = new User(id,name, password);
                use.add(user);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return use;
    }

    public List<User> selectAll() throws Exception {
        String sql = "select * from myuser where status=0";
        List<Map<String, String>> list = DBUtil.query(sql);
        List<User> users = new LinkedList<>();
        User user = null;
        for (Map<String, String> map: list) {
            user = new User(Integer.parseInt(map.get("id")),map.get("name"),map.get("password"));
            /*user.setId(Integer.parseInt(map.get("id")));
            user.setName(map.get("name"));
            user.setPassword(map.get("password"));*/
            users.add(user);
        }
        return  users;
    }

    public void save(User user){
        String sql = "insert into myuser (name, password,status) values (?,?,0)";
        String[] ss = {user.getName(), user.getPassword()};
        //这个方法可以完成增删改
        DBUtil.update(sql,ss);
    }
    public User selectById(int id) throws Exception {
        String sql = "select id,name,password from myuser where id=?";
        List<Map<String, String>> list = DBUtil.query(sql, id);
        User user = null;
        for (Map<String, String> map: list) {
            user = new User(Integer.parseInt(map.get("id")),map.get("name"),map.get("password"));
        }
        return  user;
    }
    public void update(User user) {
        String sql = "update myuser set name=?, password=? where id=?";
        Object[] ss = { user.getName(), user.getPassword(), user.getId()};
        DBUtil.update(sql,ss);
    }
    public int delete(User user){
        String sql ="update myuser set status=1 where id = ?";
//        String sql = "delete from myuser where id = ?";
        Object [] de = {user.getId()};
        int update = DBUtil.update(sql, de);
        return update;
    }

    public List<User> selectName(User user){
        String sql = "select name from myuser where name=?";
        Object [] de = {user.getName()};
        List<Map<String, String>> query = DBUtil.query(sql, de);
        List<User> users = new LinkedList<>();
        User us = null;
        for (Map<String, String> map: query) {
            us = new User(map.get("name"));
            users.add(us);
        }
        return users;
    }

    public int count() {
        String sql = "select count(id) from myuser";
        List<Map<String, String>> list = DBUtil.query(sql);

        //System.out.println();
        return Integer.parseInt(list.iterator().next().get("count(id)"));
    }

    public List<User> selectPage(int page, int size) throws Exception {
        String sql = "select * from myuser limit ?,?";
        List<Map<String, String>> list = DBUtil.query(sql, (page-1)*size, size);
        List<User> users = new LinkedList<>();
        User user = null;
        for (Map<String, String> map: list) {
            user = new User(Integer.parseInt(map.get("id")),map.get("name"),map.get("password"));
            /*user.setId(Integer.parseInt(map.get("id")));
            user.setName(map.get("name"));
            user.setPassword(map.get("password"));*/
            users.add(user);
        }
        return  users;
    }


}
